﻿/********************************************************
 * 类名： InitSaleDAL.cs
 * 功能： 销货单数据层
 *   ver	变更日		作者			变更内容
 * ────────────────────────────────────────
 *   V01.0	2014-02-10	李波			初版
 *
 * Copyright (c) 2013，李波版权所有
 ********************************************************/
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using PSS.Entity.SaleManage;
using PSS.Utility;

namespace PSS.DAL.SaleManage
{
    public class InitSaleDAL : DbHelperBase
    {
        public bool AddInitSale(InitSaleEntity model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" INSERT INTO [PSS_InitSale]([InitSaleId],[CustomerId],[DocDate],[DocNumber],[BusinessCategory],[DiscountRate],[DeductionAmount],[FoldAmount],[TheRefund],[AccountId],[Arrears],[Remark]) ");
            strSql.Append(" VALUES(@InitSaleId,@CustomerId,@DocDate,@DocNumber,@BusinessCategory,@DiscountRate,@DeductionAmount,@FoldAmount,@TheRefund,@AccountId,@Arrears,@Remark) ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@InitSaleId", model.InitSaleId);
                    Db.AddInParameter(cmd, "@CustomerId", model.CustomerId);
                    Db.AddInParameter(cmd, "@DocDate", model.DocDate);
                    Db.AddInParameter(cmd, "@DocNumber", model.DocNumber);
                    Db.AddInParameter(cmd, "@BusinessCategory", model.BusinessCategory);
                    Db.AddInParameter(cmd, "@Remark", model.Remark);
                    Db.AddInParameter(cmd, "@DiscountRate", model.DiscountRate);
                    Db.AddInParameter(cmd, "@DeductionAmount", model.DeductionAmount);
                    Db.AddInParameter(cmd, "@FoldAmount", model.FoldAmount);
                    Db.AddInParameter(cmd, "@TheRefund", model.TheRefund);
                    Db.AddInParameter(cmd, "@AccountId", model.AccountId);
                    Db.AddInParameter(cmd, "@Arrears", model.Arrears);
                    Db.AddInParameter(cmd, "@Remark", model.Remark);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public int GetTotal(InitSaleQcEntity qcmodel)
        {
            var strSql = new StringBuilder();
            strSql.Append(" SELECT COUNT(*) AS [Total] FROM PSS_InitSale AS A  ");
            strSql.Append(" INNER JOIN PSS_Customer AS B ON A.CustomerId=B.CustomerId ");
            strSql.Append(" LEFT JOIN (SELECT InitSaleId,SUM(SalesAmount) AS TotalSalesAmount FROM PSS_InitSaleDetail ");
            strSql.Append(" GROUP BY InitSaleId) AS C ON A.InitSaleId=C.InitSaleId ");
            strSql.Append(" WHERE 1=1 ");
            if (!string.IsNullOrEmpty(qcmodel.DocNumber))
            {
                strSql.Append(" AND A.DocNumber LIKE '%" + qcmodel.DocNumber + "%' ");
            }
            if (!string.IsNullOrEmpty(qcmodel.CustomerId))
            {
                strSql.Append(" AND A.CustomerId=@CustomerId ");
            }
            if (!string.IsNullOrEmpty(qcmodel.DocDateFrom))
            {
                strSql.Append(" AND A.DocDate >=@SDocDate");
            }
            if (!string.IsNullOrEmpty(qcmodel.DocDateEnd))
            {
                strSql.Append(" AND A.DocDate <=@EDocDate");
            }
            var count = 0;
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(qcmodel.DocDateFrom))
                    {
                        Db.AddInParameter(cmd, "@SDocDate", DbType.DateTime, Convert.ToDateTime(qcmodel.DocDateFrom));
                    }
                    if (!string.IsNullOrEmpty(qcmodel.DocDateEnd))
                    {
                        Db.AddInParameter(cmd, "@EDocDate", DbType.DateTime, Convert.ToDateTime(qcmodel.DocDateEnd));
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["Total"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }

        public IList<InitSaleEntity> GetInitSaleList(InitSaleQcEntity qcmodel)
        {
            var list = new List<InitSaleEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            var start = qcmodel.PageSize * qcmodel.PageIndex + 1;
            var end = qcmodel.PageSize + start - 1;
            strSql.Append(" SELECT T.* FROM( ");
            strSql.Append(" SELECT row_number() over (order by A.DocNumber desc) AS idx,A.InitSaleId,A.DocDate,A.DocNumber,A.BusinessCategory,B.CustomerName,C.TotalSalesAmount,A.FoldAmount,A.TheRefund FROM PSS_InitSale AS A  ");
            strSql.Append(" INNER JOIN PSS_Customer AS B ON A.CustomerId=B.CustomerId ");
            strSql.Append(" LEFT JOIN (SELECT InitSaleId,SUM(SalesAmount) AS TotalSalesAmount FROM PSS_InitSaleDetail ");
            strSql.Append(" GROUP BY InitSaleId) AS C ON A.InitSaleId=C.InitSaleId ");
            strSql.Append(" WHERE 1=1 ");
            if (!string.IsNullOrEmpty(qcmodel.DocNumber))
            {
                strSql.Append(" AND A.DocNumber LIKE '%" + qcmodel.DocNumber + "%' ");
            }
            if (!string.IsNullOrEmpty(qcmodel.CustomerId))
            {
                strSql.Append(" AND A.CustomerId=@CustomerId ");
            }
            if (!string.IsNullOrEmpty(qcmodel.DocDateFrom))
            {
                strSql.Append(" AND A.DocDate >=@SDocDate");
            }
            if (!string.IsNullOrEmpty(qcmodel.DocDateEnd))
            {
                strSql.Append(" AND A.DocDate <=@EDocDate");
            }
            strSql.Append(") AS T ");
            strSql.Append(" Where T.idx between " + start + " and " + end);
            if (qcmodel.SortField != "" && qcmodel.SortOrder != "")
            {
                strSql.Append(" Order by " + qcmodel.SortField + " " + qcmodel.SortOrder);
            }
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(qcmodel.DocDateFrom))
                    {
                        Db.AddInParameter(cmd, "@SDocDate", DbType.DateTime, Convert.ToDateTime(qcmodel.DocDateFrom));
                    }
                    if (!string.IsNullOrEmpty(qcmodel.DocDateEnd))
                    {
                        Db.AddInParameter(cmd, "@EDocDate", DbType.DateTime, Convert.ToDateTime(qcmodel.DocDateEnd));
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new InitSaleEntity();
                            if (reader["InitSaleId"] != DBNull.Value)
                                model.InitSaleId = new Guid(reader["InitSaleId"].ToString());
                            if (reader["DocDate"] != DBNull.Value)
                                model.DocDate = Convert.ToDateTime(reader["DocDate"].ToString());
                            model.DocNumber = reader["DocNumber"] == obj ? null : Convert.ToString(reader["DocNumber"]);
                            model.CustomerName = reader["CustomerName"] == obj ? null : Convert.ToString(reader["CustomerName"]);
                            if (reader["BusinessCategory"] != DBNull.Value)
                                model.BusinessCategory = Convert.ToInt32(reader["BusinessCategory"].ToString());
                            if (reader["TotalSalesAmount"] != DBNull.Value)
                                model.TheRefund = Convert.ToDecimal(reader["TotalSalesAmount"].ToString());
                            if (reader["FoldAmount"] != DBNull.Value)
                                model.FoldAmount = Convert.ToDecimal(reader["FoldAmount"].ToString());
                            if (reader["TheRefund"] != DBNull.Value)
                                model.TheRefund = Convert.ToDecimal(reader["TheRefund"].ToString());
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public bool ModifyInitSale(InitSaleEntity model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" UPDATE [PSS_InitSale] SET [CustomerId] = @CustomerId,[DocDate] = @DocDate,[DocNumber] = @DocNumber,[BusinessCategory] = @BusinessCategory,[DiscountRate] = @DiscountRate,[DeductionAmount] = @DeductionAmount,[FoldAmount] = @FoldAmount,[TheRefund] = @TheRefund,[AccountId] = @AccountId,[Arrears] = @Arrears,[Remark] = @Remark ");
            strSql.Append(" WHERE [InitSaleId] = @InitSaleId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@CustomerId", model.CustomerId);
                    Db.AddInParameter(cmd, "@DocDate", model.DocDate);
                    Db.AddInParameter(cmd, "@DocNumber", model.DocNumber);
                    Db.AddInParameter(cmd, "@BusinessCategory", model.BusinessCategory);
                    Db.AddInParameter(cmd, "@Remark", model.Remark);
                    Db.AddInParameter(cmd, "@DiscountRate", model.DiscountRate);
                    Db.AddInParameter(cmd, "@DeductionAmount", model.DeductionAmount);
                    Db.AddInParameter(cmd, "@FoldAmount", model.FoldAmount);
                    Db.AddInParameter(cmd, "@TheRefund", model.TheRefund);
                    Db.AddInParameter(cmd, "@AccountId", model.AccountId);
                    Db.AddInParameter(cmd, "@Arrears", model.Arrears);
                    Db.AddInParameter(cmd, "@InitSaleId", model.InitSaleId);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool RemoveInitSale(string id)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" DELETE FROM [PSS_InitSale] WHERE InitSaleId=@InitSaleId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@InitSaleId", id);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public InitSaleEntity GetInitSaleInfo(string id)
        {
            var model = new InitSaleEntity();
            object obj = DBNull.Value;
            var strSql = new StringBuilder();
            strSql.Append(" SELECT [InitSaleId],[CustomerId],[DocDate],[DocNumber],[BusinessCategory],[DiscountRate],[DeductionAmount],[FoldAmount],[TheRefund],[AccountId],[Arrears],[Remark],[CreateTime] ");
            strSql.Append(" FROM [PSS_InitSale] where InitSaleId=@InitSaleId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@InitSaleId", id);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            if (reader["InitSaleId"] != DBNull.Value)
                                model.InitSaleId = new Guid(reader["InitSaleId"].ToString());
                            if (reader["CustomerId"] != DBNull.Value)
                                model.CustomerId = new Guid(reader["CustomerId"].ToString());
                            if (reader["DocDate"] != DBNull.Value)
                                model.DocDate = Convert.ToDateTime(reader["DocDate"].ToString());
                            model.DocNumber = reader["DocNumber"] == obj ? null : Convert.ToString(reader["DocNumber"]);
                            if (reader["BusinessCategory"] != DBNull.Value)
                                model.BusinessCategory = Convert.ToInt32(reader["BusinessCategory"].ToString());
                            model.Remark = reader["Remark"] == obj ? null : Convert.ToString(reader["Remark"]);
                            if (reader["DiscountRate"] != DBNull.Value)
                                model.DiscountRate = Convert.ToDecimal(reader["DiscountRate"].ToString());
                            if (reader["DeductionAmount"] != DBNull.Value)
                                model.DeductionAmount = Convert.ToDecimal(reader["DeductionAmount"].ToString());
                            if (reader["FoldAmount"] != DBNull.Value)
                                model.FoldAmount = Convert.ToDecimal(reader["FoldAmount"].ToString());
                            if (reader["TheRefund"] != DBNull.Value)
                                model.TheRefund = Convert.ToDecimal(reader["TheRefund"].ToString());
                            if (reader["AccountId"] != DBNull.Value)
                                model.AccountId = new Guid(reader["AccountId"].ToString());
                            if (reader["Arrears"] != DBNull.Value)
                                model.Arrears = Convert.ToDecimal(reader["Arrears"].ToString());
                            model.Remark = reader["Remark"] == obj ? null : Convert.ToString(reader["Remark"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return model;
            }
        }
    }
}
